package persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import persistence.exception.DAOException;
import persistence.util.JdbcUtil;

public class LibraryLocationDAOJdbcImpl extends JdbcDAOBase implements LibraryLocationDAO {

	private CityDAO cityDAO;
	
	public LibraryLocationDAOJdbcImpl() {
	}	
	
	public CityDAO getCityDAO() {
		return cityDAO;
	}
	
	public void setCityDAO(CityDAO cityDAO) {
		this.cityDAO = cityDAO;
	}

	@Override
	public LibraryLocation find(int id) throws DAOException{
		
		String sql = "SELECT id, orte_id FROM bibliotheksstandorte WHERE id = ?";
		LibraryLocation libraryLocation = null;
		City city = null;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			con = getConProvider().getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				city = cityDAO.find(rs.getInt(2));
				libraryLocation = new LibraryLocation(rs.getInt(1), city);
			}
		}
		catch (SQLException se) {
			throw new DAOException("Error when attempting to find library location with id: " + id, se);
		}
		finally {
			JdbcUtil.cleanup(con, stmt, rs);
		}
		return libraryLocation;
	}

	@Override
	public List<LibraryLocation> findAll() throws DAOException {
		String sql = "SELECT id, orte_id FROM bibliotheksstandorte";
		List<LibraryLocation> locations = new ArrayList<LibraryLocation>();
		City city = null;
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			con = getConProvider().getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			
			while (rs.next()) {
				city = cityDAO.find(rs.getInt(2));
				locations.add(new LibraryLocation(rs.getInt(1), city));
			}
		}
		catch(SQLException se) {
			throw new DAOException("Problem when attempting to get all LibraryLocations", se);
		}
		finally {
			JdbcUtil.cleanup(con, stmt, rs);
		}
		return locations;
	}

}
